import sqlite3
import os
from typing import List, Dict, Any, Optional
from contextlib import contextmanager

class Database:
    def __init__(self, db_path: str = "database.sqlite3"):
        """
        初始化数据库连接
        :param db_path: 数据库文件路径
        """
        self.db_path = db_path
        self.init_database()
    
    def init_database(self):
        """初始化数据库，创建表结构"""
        with self.get_connection() as conn:
            cursor = conn.cursor()
            
            # 创建learn表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS learn (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    learnId VARCHAR(100) NOT NULL UNIQUE,
                    process VARCHAR(200) NOT NULL,
                    procedure VARCHAR(200) NOT NULL,
                    subProcedure VARCHAR(200) NOT NULL,
                    standardSolution VARCHAR(200) NOT NULL,
                    standardName VARCHAR(200) NOT NULL,
                    system VARCHAR(100) NOT NULL,
                    platform VARCHAR(100) NOT NULL,
                    lineType VARCHAR(100) NOT NULL,
                    failureMode VARCHAR(200) NOT NULL,
                    schematicDiagram VARCHAR(200) NOT NULL,
                    fixCateory VARCHAR(100) NOT NULL,
                    standardFileNo VARCHAR(100) NOT NULL,
                    controlStatus VARCHAR(50) NOT NULL,
                    landedStatus VARCHAR(50) NOT NULL,
                    subSystemRelatedItems TEXT NOT NULL,
                    creatorId VARCHAR(50) NOT NULL,
                    updaterId VARCHAR(50),
                    createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建lesson表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS lesson (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    lessonId VARCHAR(100) NOT NULL UNIQUE,
                    source VARCHAR(200) NOT NULL,
                    process VARCHAR(200) NOT NULL,
                    factory VARCHAR(50) NOT NULL,
                    projectName VARCHAR(200) NOT NULL,
                    occurenceTime VARCHAR(50) NOT NULL,
                    problemType VARCHAR(200) NOT NULL,
                    faReport VARCHAR(200) NOT NULL,
                    problemDescription TEXT NOT NULL,
                    improvement TEXT NOT NULL,
                    creator VARCHAR(50) NOT NULL,
                    updater VARCHAR(50),
                    createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建learn_lesson关联表（多对多关系）
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS learn_lesson (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    learn_id INTEGER NOT NULL,
                    lesson_id INTEGER NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (learn_id) REFERENCES learn (id) ON DELETE CASCADE,
                    FOREIGN KEY (lesson_id) REFERENCES lesson (id) ON DELETE CASCADE,
                    UNIQUE(learn_id, lesson_id)
                )
            ''')
            
            conn.commit()
    
    @contextmanager
    def get_connection(self):
        """获取数据库连接的上下文管理器"""
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row  # 使结果可以按列名访问
        try:
            yield conn
        finally:
            conn.close()
    
    def execute_query(self, query: str, params: tuple = ()) -> List[Dict[str, Any]]:
        """
        执行查询语句
        :param query: SQL查询语句
        :param params: 查询参数
        :return: 查询结果列表
        """
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, params)
            return [dict(row) for row in cursor.fetchall()]
    
    def execute_update(self, query: str, params: tuple = ()) -> int:
        """
        执行更新语句（INSERT, UPDATE, DELETE）
        :param query: SQL语句
        :param params: 参数
        :return: 受影响的行数
        """
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, params)
            conn.commit()
            return cursor.rowcount
    
    def execute_insert(self, query: str, params: tuple = ()) -> int:
        """
        执行插入语句并返回新插入记录的ID
        :param query: SQL插入语句
        :param params: 参数
        :return: 新插入记录的ID
        """
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, params)
            conn.commit()
            return cursor.lastrowid
    
    def get_by_id(self, table: str, record_id: int) -> Optional[Dict[str, Any]]:
        """
        根据ID获取单条记录
        :param table: 表名
        :param record_id: 记录ID
        :return: 记录字典或None
        """
        query = f"SELECT * FROM {table} WHERE id = ?"
        results = self.execute_query(query, (record_id,))
        return results[0] if results else None
    
    def get_all(self, table: str, limit: int = None, offset: int = 0) -> List[Dict[str, Any]]:
        """
        获取表中所有记录
        :param table: 表名
        :param limit: 限制数量
        :param offset: 偏移量
        :return: 记录列表
        """
        query = f"SELECT * FROM {table}"
        if limit:
            query += f" LIMIT {limit} OFFSET {offset}"
        return self.execute_query(query)
    
    def delete_by_id(self, table: str, record_id: int) -> bool:
        """
        根据ID删除记录
        :param table: 表名
        :param record_id: 记录ID
        :return: 是否删除成功
        """
        query = f"DELETE FROM {table} WHERE id = ?"
        affected_rows = self.execute_update(query, (record_id,))
        return affected_rows > 0
    
    def clear_table(self, table: str) -> bool:
        """
        清空表数据
        :param table: 表名
        :return: 是否成功
        """
        query = f"DELETE FROM {table}"
        self.execute_update(query)
        return True
    
    def get_table_count(self, table: str) -> int:
        """
        获取表中记录数量
        :param table: 表名
        :return: 记录数量
        """
        query = f"SELECT COUNT(*) as count FROM {table}"
        result = self.execute_query(query)
        return result[0]['count'] if result else 0

# 创建全局数据库实例
db = Database()
